﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DTO;
using System.Data.SqlClient;

namespace DAO
{
    public class Connnection
    {
        public static SqlConnection TaoKetNoi()
        {
            SqlConnection cnn = new SqlConnection(@"Data Source=TRANTHANH-PC\SQLEXPRESS;Initial Catalog=BanSach;Integrated Security=True");
            return cnn;
        }
    }
    public class NhanVien_DAO
    {
        //load danh sach NhanVien:
        public static DataTable LoadDSNhanVien()
        {
            SqlConnection cnn = Connnection.TaoKetNoi();
            SqlCommand cmd = new SqlCommand("SelectAll_NV", cnn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dtb = new DataTable();
            da.Fill(dtb);
            return dtb;
        }
        //Them NhanVien Moi:
        public static void ThemNhanVien(NhanVien_DTO nv)
        {
            SqlConnection cnn = Connnection.TaoKetNoi();
            SqlCommand cmd = new SqlCommand("insert_NhanVien", cnn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@maNV", SqlDbType.NChar, 10);
            cmd.Parameters.Add("@tenNV", SqlDbType.NVarChar, 50);
            cmd.Parameters.Add("@QueQuan", SqlDbType.NVarChar, 50);
            cmd.Parameters.Add("@SDT", SqlDbType.Char, 15);
            cmd.Parameters.Add("@Gioitinh", SqlDbType.NVarChar, 5);
            cmd.Parameters.Add("@Noio", SqlDbType.NVarChar, 50);
            cmd.Parameters.Add("@ChucVu", SqlDbType.NVarChar, 50);
            //gan:
            cmd.Parameters["@maNV"].Value = nv.MaNV;
            cmd.Parameters["@tenNV"].Value = nv.TenNV;
            cmd.Parameters["@QueQuan"].Value = nv.QueQuan;
            cmd.Parameters["@SDT"].Value = nv.SDT;
            cmd.Parameters["@Gioitinh"].Value = nv.GioiTinh;
            cmd.Parameters["@Noio"].Value = nv.NoiO;
            cmd.Parameters["@ChucVu"].Value = nv.ChucVu;
            cnn.Open();
            cmd.ExecuteNonQuery();
            cnn.Close();
        }
        //Sua NhanVien:
        public static void SuaNhanVien(NhanVien_DTO sua)
        {
            SqlConnection cnn = Connnection.TaoKetNoi();
            SqlCommand cmd = new SqlCommand("Update_NhanVien", cnn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@maNV", SqlDbType.NChar, 10);
            cmd.Parameters.Add("@tenNV", SqlDbType.NVarChar, 50);
            cmd.Parameters.Add("@QueQuan", SqlDbType.NVarChar, 50);
            cmd.Parameters.Add("@SDT", SqlDbType.Char, 15);
            cmd.Parameters.Add("@Gioitinh", SqlDbType.NVarChar, 5);
            cmd.Parameters.Add("@Noio", SqlDbType.NVarChar, 50);
            cmd.Parameters.Add("@ChucVu", SqlDbType.NVarChar, 50);
            //gan:
            cmd.Parameters["@maNV"].Value = sua.MaNV;
            cmd.Parameters["@tenNV"].Value = sua.TenNV;
            cmd.Parameters["@QueQuan"].Value = sua.QueQuan;
            cmd.Parameters["@SDT"].Value = sua.SDT;
            cmd.Parameters["@Gioitinh"].Value = sua.GioiTinh;
            cmd.Parameters["@Noio"].Value = sua.NoiO;
            cmd.Parameters["@ChucVu"].Value = sua.ChucVu;
            cnn.Open();
            cmd.ExecuteNonQuery();
            cnn.Close();
        }
        //XoaNhanVien:
        public static void XoaNhanVien(string manv)
        {
            SqlConnection cnn = Connnection.TaoKetNoi();
            SqlCommand cmd = new SqlCommand("[Delete_NhanVien]", cnn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@Manv", SqlDbType.NChar, 10);
            cmd.Parameters["@Manv"].Value = manv;
            cnn.Open();
            cmd.ExecuteNonQuery();
            cnn.Close();
        }
        //TimMaNV:
        public static DataTable TimMaNV(string ma)
        {
            SqlConnection cnn = Connnection.TaoKetNoi();
            SqlCommand cmd = new SqlCommand("TimTheoMaNV", cnn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@code", SqlDbType.NChar, 10);
            cmd.Parameters["@code"].Value = ma;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dtb = new DataTable();
            da.Fill(dtb);
            return dtb;
        }
        //TimTenNV:
        public static DataTable TimTenNV(string ten)
        {
            SqlConnection cnn = Connnection.TaoKetNoi();
            SqlCommand cmd = new SqlCommand("TimTheoTenNV", cnn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@code", SqlDbType.NVarChar, 50);
            cmd.Parameters["@code"].Value = ten;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dtb = new DataTable();
            da.Fill(dtb);
            return dtb;
        }
        //TimQueQuan:
        public static DataTable TimQueQuan(string que)
        {
            SqlConnection cnn = Connnection.TaoKetNoi();
            SqlCommand cmd = new SqlCommand("TimTheoQueQuanNV", cnn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@code", SqlDbType.NVarChar, 50);
            cmd.Parameters["@code"].Value = que;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dtb = new DataTable();
            da.Fill(dtb);
            return dtb;
        }
        //TimChucVu:
        public static DataTable TimChucVu(string chuc)
        {
            SqlConnection cnn = Connnection.TaoKetNoi();
            SqlCommand cmd = new SqlCommand("TimTheoChucVuNV", cnn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@code", SqlDbType.NVarChar, 50);
            cmd.Parameters["@code"].Value = chuc;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dtb = new DataTable();
            da.Fill(dtb);
            return dtb;
        }
    }
}
